const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库配置
const dbConfig = {
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'cmb_restaurant',
  charset: 'utf8mb4'
};

// 图片文件名映射表 - 数据库中的错误文件名 -> 实际正确的文件名
const imageNameMapping = {
  'HighBallMpage@1x.png': 'HighBallMpage_@1x.png',
  'MainPIceC@1x.png': 'MainPiceC_@1x.png',
  // 可以根据需要添加更多映射
};

async function fixImageUrls() {
  let connection;
  
  try {
    console.log('连接数据库...');
    connection = await mysql.createConnection(dbConfig);
    
    // 获取所有使用/images/menu/路径的菜品
    console.log('查询使用旧图片路径的菜品...');
    const [rows] = await connection.execute(
      `SELECT id, name, image_url FROM menu_items 
       WHERE image_url LIKE '/images/menu/%' 
       AND deleted_at IS NULL
       ORDER BY id`
    );
    
    console.log(`找到 ${rows.length} 个使用旧图片路径的菜品`);
    
    const menuImagesDir = path.join(__dirname, '../public/images/menu');
    let fixedCount = 0;
    let notFoundCount = 0;
    
    for (const row of rows) {
      const { id, name, image_url } = row;
      console.log(`\n处理菜品 ID ${id}: ${name}`);
      console.log(`当前图片URL: ${image_url}`);
      
      // 提取文件名
      const fileName = path.basename(image_url);
      console.log(`提取的文件名: ${fileName}`);
      
      // 检查是否需要映射
      const actualFileName = imageNameMapping[fileName] || fileName;
      console.log(`实际文件名: ${actualFileName}`);
      
      // 检查实际文件是否存在
      const actualFilePath = path.join(menuImagesDir, actualFileName);
      const fileExists = fs.existsSync(actualFilePath);
      console.log(`文件存在: ${fileExists}`);
      
      if (fileExists) {
        // 如果文件名需要更新
        if (actualFileName !== fileName) {
          const newImageUrl = `/images/menu/${actualFileName}`;
          console.log(`更新图片URL: ${image_url} -> ${newImageUrl}`);
          
          await connection.execute(
            'UPDATE menu_items SET image_url = ?, updated_at = NOW() WHERE id = ?',
            [newImageUrl, id]
          );
          
          fixedCount++;
          console.log(`✅ 已修正菜品 ID ${id} 的图片URL`);
        } else {
          console.log(`✅ 菜品 ID ${id} 的图片URL已正确`);
        }
      } else {
        console.log(`❌ 菜品 ID ${id} 的图片文件不存在: ${actualFilePath}`);
        notFoundCount++;
      }
    }
    
    console.log(`\n修正完成:`);
    console.log(`- 已修正: ${fixedCount} 个菜品`);
    console.log(`- 文件不存在: ${notFoundCount} 个菜品`);
    console.log(`- 总计处理: ${rows.length} 个菜品`);
    
  } catch (error) {
    console.error('修正图片URL时发生错误:', error);
  } finally {
    if (connection) {
      await connection.end();
      console.log('数据库连接已关闭');
    }
  }
}

// 运行脚本
if (require.main === module) {
  fixImageUrls();
}

module.exports = { fixImageUrls };